alter function f_purpriceany
(
  @bdate datetime,
  @edate datetime,
  @currfilid int,
  @anykind tinyint
)
returns @resultbill table
(
  bid       int,
  bno       int,
  btype     int,
  bcode     nvarchar(60),
  bdate     datetime,
  empid     int,
  traderid  int,
  deptid    int, 
  create_by int, 
  check_by  int,
  filid     int,
  remark    nvarchar(200),

  productid int,
  p_code    nvarchar(40),
  p_name    nvarchar(60),
  p_spec    nvarchar(60),
  unitid    int,
  unitname  nvarchar(60),
  price     decimal(18,8),
  quantity  decimal(18,4),
  amount    decimal(18,2),

  purreprice decimal(18,8),
  repre     decimal(18,8),
  priorprice decimal(18,8),
  risepre   decimal(18,8)
)
as
begin
  if @anykind=0
    insert into @resultbill
    select m.bid,d.bno,140 btype,bcode,bdate
      ,m.empid,m.traderid,m.deptid,m.create_by,m.check_by,m.filid,m.remark
      ,d.productid,p.code p_code,p.name p_name,p.spec p_spec,d.unitid,u.unitname,d.price,d.quantity,d.amount
      ,u.purreprice,((d.price-case when u.purreprice>0 then u.purreprice else d.price end)/case when u.purreprice>0 then u.purreprice else d.price end)*100 repre
      ,0.00 as priorprice,0.00 as risepre
      from tb_purchase m,tb_purchasedtl d,td_unitprice u,td_product p
      where m.bdate>=@bdate and m.bdate<=@edate
        and (m.filid=@currfilid or @currfilid=-99)
        and m.bid=d.bid and d.unitid=u.unitid and d.price<>0 and d.productid=p.p_id and d.quantity>0
        and m.bstate=1 and (m.bkind=1 or m.bkind=4)
      order by d.productid,d.unitid,m.bdate,d.borderno
  else
    insert into @resultbill
    select m.bid,d.bno,140 btype,bcode,bdate
      ,m.empid,m.traderid,m.deptid,m.create_by,m.check_by,m.filid,m.remark
      ,d.productid,p.code p_code,p.name p_name,p.spec p_spec,d.unitid,up.unitname,d.price,d.quantity,d.amount
      ,u.price purreprice,((d.price-case when u.price>0 then u.price else d.price end)/case when u.price>0 then u.price else d.price end)*100 repre
      ,0.00 as priorprice,0.00 as risepre
      from tb_purchase m,tb_purchasedtl d,td_traderproduct u,td_unitprice up,td_product p
      where m.bdate>=@bdate and m.bdate<=@edate
        and (m.filid=@currfilid or @currfilid=-99)
        and m.traderid=u.traderid and m.bid=d.bid and d.unitid=u.unitid and d.productid=p.p_id and d.quantity>0
        and m.bstate=1 and (m.bkind=1 or m.bkind=4)
      order by d.productid,d.unitid,m.bdate,d.borderno
  
  declare @pbdate datetime,@bid int,@bno int,@productid int,@unitid int, @priorprice decimal(18,8)    
  declare cur_prior cursor for select bdate,bid,bno,productid,unitid from @resultbill
  open cur_prior 
  fetch next from cur_prior into @pbdate,@bid,@bno,@productid,@unitid
  while @@fetch_status = 0 
  begin
    set @priorprice=null
    select top 1 @priorprice=price from tt_bill 
      where btype=140 and bdate<@pbdate and bstate=1 and productid=@productid and unitid=@unitid and quantity>0 order by bdate desc
    if isnull(@priorprice,0)>0
      update @resultbill set priorprice=@priorprice,risepre=((price-@priorprice)/price)*100
        where bid=@bid and bno=@bno
    fetch next from cur_prior into @pbdate,@bid,@bno,@productid,@unitid
  end
  close cur_prior
  deallocate cur_prior  
  return
end





